{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cba24704-902a-46ef-98ec-6d04560ceed7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "# openpyxl.__version__\n",
    "# CMIS Lower Page:\n",
    "dump1_f = open('dump1.txt', 'r')\n",
    "dump1_f_dict = {}\n",
    "\n",
    "k = \"\"\n",
    "v = \"\"\n",
    "for f in dump1_f:\n",
    "    if \":\" in f:\n",
    "        k = f.split(':')[0].split('CMIS')[1].strip(' ')\n",
    "        continue\n",
    "    v = f.replace('\\n','').split(' ')\n",
    "    if k == '':\n",
    "        continue\n",
    "    dump1_f_dict.update({k: v})\n",
    "\n",
    "# print(dump1_f_dict)\n",
    "for k,v in dump1_f_dict.items():\n",
    "    print(k)\n",
    "    print(v)\n",
    "    print('----------------------------------------------------')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "9fb5791c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No Summary Copy sheet...\n",
      "Create a Summary Copy sheet, Ok.\n"
     ]
    }
   ],
   "source": [
    "# copy a sheet\n",
    "from openpyxl import load_workbook\n",
    "import os\n",
    "from openpyxl.styles import colors, Font, Fill, NamedStyle\n",
    "from openpyxl.styles import PatternFill, Border, Side, Alignment\n",
    "wb1 = load_workbook(\"temp.xlsm\", data_only=True)\n",
    "try:\n",
    "    #if Sumarry Copy exist remove first!\n",
    "    del wb1[\"Summary Copy\"]\n",
    "except:\n",
    "    print(\"No Summary Copy sheet...\")\n",
    "# wb1 = load_workbook(old_file)\n",
    "# print(wb.sheetnames)\n",
    "sheet1 = wb1[\"Summary\"]\n",
    "# sheet1 = wb1.active\n",
    "\n",
    "try:\n",
    "    print(\"Create a Summary Copy sheet, Ok.\")\n",
    "    target = wb1.copy_worksheet(sheet1)\n",
    "    wb1.save(filename=\"temp1.xlsm\")\n",
    "except Exception as e:\n",
    "    print(str(e))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "971ce85d-63e8-41ea-8108-6d14d84a8f88",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from openpyxl import load_workbook\n",
    "from openpyxl.styles import colors, Font, Fill, NamedStyle\n",
    "from openpyxl.styles import PatternFill, Border, Side, Alignment\n",
    "\n",
    "wb1 = load_workbook(\"temp.xlsm\", data_only=True)\n",
    "# wb1 = load_workbook(old_file)\n",
    "# print(wb.sheetnames)\n",
    "sheet1 = wb1[\"Summary\"]\n",
    "# sheet1 = wb1.active\n",
    "\n",
    "rows1 = sheet1.max_row\n",
    "cols1 = sheet1.max_column\n",
    "\n",
    "print(\"row1:{} cols:{}\".format(rows1, cols1))\n",
    "\n",
    "rows_idx = 0\n",
    "cols_idx = 0\n",
    "\n",
    "temp_summary_dict = {}\n",
    "\n",
    "#load lower data\n",
    "for col in sheet1.iter_cols(min_col=2, max_col=2, min_row=3, max_row=rows1-1):\n",
    "    v = []\n",
    "    for cell in col:\n",
    "        v.append(cell.value)\n",
    "    temp_summary_dict.update({'Lower Page' : v[1:]})\n",
    "\n",
    "#load upper data\n",
    "for col in sheet1.iter_cols(min_col=4, max_col=cols1, min_row=3, max_row=rows1-1):\n",
    "    v = []\n",
    "    for cell in col:\n",
    "        v.append(cell.value)\n",
    "    if len(str(v[0])) == 1:\n",
    "        p = '0' + str(v[0])\n",
    "    else:\n",
    "        p = str(v[0])\n",
    "    temp_summary_dict.update({'Upper Page '+ p + 'h' : v[1:]})\n",
    "\n",
    "\n",
    "# print(temp_summary_dict)   \n",
    "for k,v in temp_summary_dict.items():\n",
    "    print(k)\n",
    "    print(v)\n",
    "    print('-----------------------------------------------------')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0eea25ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from openpyxl import load_workbook\n",
    "from openpyxl.styles import NamedStyle\n",
    "from openpyxl.styles import PatternFill, Border, Side, Alignment\n",
    "from openpyxl.styles.colors import Color\n",
    "\n",
    "#---------------------------------------------------------------\n",
    "#orange border\n",
    "line_3 = Side(style='thick', color='FACC2E')\n",
    "border3 = Border(top=line_3, bottom=line_3, left=line_3, right=line_3)\n",
    "# alignment\n",
    "alignment = Alignment(horizontal='left', vertical='center')\n",
    "# yellow\n",
    "keep_sty = NamedStyle(name='keep_sty', fill=PatternFill('solid', fgColor='ffff00'), alignment=alignment)\n",
    "#red\n",
    "diff_sty = NamedStyle(name='diff_sty', fill=PatternFill('solid', fgColor='FF0033'), alignment=alignment)\n",
    "#green\n",
    "same_sty = NamedStyle(name='same_sty', fill=PatternFill('solid', fgColor='339933'), alignment=alignment)\n",
    "# has blank space\n",
    "has_blank_space_sty = NamedStyle(name='has_blank_space_sty',  border=border3, alignment=alignment)\n",
    "#--------------------------------------------------------------\n",
    "\n",
    "\n",
    "def load_dump_data(filename):\n",
    "    dump1_f = open(filename, 'r')\n",
    "    dump1_f_dict = {}\n",
    "    k = \"\"\n",
    "    v = \"\"\n",
    "    for f in dump1_f:\n",
    "        if \":\" in f:\n",
    "            k = f.split(':')[0].split('CMIS')[1].strip(' ')\n",
    "            continue\n",
    "        v = f.replace('\\n','').split(' ')\n",
    "        if k == '':\n",
    "            continue\n",
    "        dump1_f_dict.update({k: v})\n",
    "    # print(dump1_f_dict)\n",
    "    # for k,v in dump1_f_dict.items():\n",
    "    #     print(k)\n",
    "    #     print(v)\n",
    "    #     print('----------------------------------------------------')\n",
    "    return dump1_f_dict\n",
    "\n",
    "\n",
    "def load_temp_data_and_copy_summary_sheet(filename):\n",
    "    wb = load_workbook(filename, data_only=True, keep_vba=True)\n",
    "    try:\n",
    "        #if Sumarry Copy exist remove first!\n",
    "        del wb[\"Summary Copy\"]\n",
    "    except:\n",
    "        print(\"No Summary Copy sheet, create it now ...\")\n",
    "    # print(wb.sheetnames)\n",
    "    sheet1 = wb[\"Summary\"]\n",
    "\n",
    "    try:\n",
    "        print(\"Create a Summary Copy sheet, Ok.\")\n",
    "        target = wb.copy_worksheet(sheet1)\n",
    "    except Exception as e:\n",
    "        print(str(e))\n",
    "    return wb\n",
    "\n",
    "\n",
    "def showVersion():\n",
    "    print(\"   ________  ____________    ______                                    \")\n",
    "    print(\"  / ____/  |/  /  _/ ___/   / ____/___  ____ ___  ____  ____ _________ \")\n",
    "    print(\" / /   / /|_/ // / \\__ \\   / /   / __ \\/ __ `__ \\/ __ \\/ __ `/ ___/ _ \\\\\")\n",
    "    print(\"/ /___/ /  / // / ___/ /  / /___/ /_/ / / / / / / /_/ / /_/ / /  /  __/\")\n",
    "    print(\"\\____/_/  /_/___//____/   \\____/\\____/_/ /_/ /_/ .___/\\__,_/_/   \\___/ \")\n",
    "    print(\"                                              /_/                      \")\n",
    "    print(\"  ______            __\")\n",
    "    print(\" /_  __/___  ____  / /\")\n",
    "    print(\"  / / / __ \\/ __ \\/ / \")\n",
    "    print(\" / / / /_/ / /_/ / /  \")\n",
    "    print(\"/_/  \\____/\\____/_/   \")\n",
    "    print(\"                      \")\n",
    "    print('-------------------------------------------')\n",
    "    print(\"CMIS Compare Tool\")\n",
    "    print(\"Version: 0.0.1\")\n",
    "    print(\"Email: kai.zeng@lumentum.com\")\n",
    "    print('-------------------------------------------')\n",
    "\n",
    "\n",
    "def selectCompareFiles():\n",
    "    docs = os.listdir()\n",
    "    docs_dict = {}\n",
    "    doc_idx = 1\n",
    "    for doc in docs:\n",
    "        if '.xlsx' in doc or '.xlsm' in doc or '.txt' in doc:\n",
    "            docs_dict.update({doc_idx : doc})\n",
    "            doc_idx = doc_idx + 1\n",
    "\n",
    "    print(5*'---' + 'Current Files' + 5*'---')\n",
    "    # print(docs_dict)\n",
    "    for k,v in docs_dict.items():\n",
    "        print(\"{}. {}\".format(k, v))\n",
    "    print('-------------------------------------------')\n",
    "\n",
    "    template_file_idx = int(input(\"Please input Template file index: \"))\n",
    "    while template_file_idx not in docs_dict.keys():\n",
    "        print('File NOT exist! Please select again.')\n",
    "        template_file_idx = int(input(\"Please input Template file index: \"))\n",
    "\n",
    "    dump_file_idx = int(input(\"Please input Dump file index: \"))\n",
    "    while dump_file_idx not in docs_dict.keys():\n",
    "        print('File NOT exist! Please select again.')\n",
    "        dump_file_idx = int(input(\"Please input Dump file index: \"))\n",
    "\n",
    "    return (docs_dict[template_file_idx], docs_dict[dump_file_idx])\n",
    "\n",
    "\n",
    "\n",
    "def startCompareFiles(template_file, dump_file):\n",
    "    wb = load_temp_data_and_copy_summary_sheet(template_file)\n",
    "    sheet = wb[\"Summary Copy\"]\n",
    "\n",
    "    max_row = sheet.max_row\n",
    "    max_column = sheet.max_column\n",
    "    # print(\"row1:{} cols:{}\".format(max_row, max_column))\n",
    "\n",
    "    #load dump data\n",
    "    dump_data_dict = load_dump_data(dump_file)\n",
    "\n",
    "    # compare lower data, Lower Page\n",
    "    print(\"Start compare Lower page data ...\")\n",
    "    for col in sheet.iter_cols(min_col=2, max_col=2, min_row=3, max_row=max_row-1):\n",
    "        v = []\n",
    "        cell_idx = 0\n",
    "        for cell in col:\n",
    "            # print(cell.value)\n",
    "            cell_idx = cell_idx + 1\n",
    "            if cell_idx == 1:\n",
    "                continue\n",
    "\n",
    "            if len(str(cell.value)) == 1:\n",
    "                cell_val = '0' + str(cell.value)\n",
    "            else:\n",
    "                cell_val = str(cell.value)\n",
    "                \n",
    "            print(\"Lower Page {}h/{}  Template Data:{} <---> Dump Data:{}\".format( 0, cell_idx-2, cell_val,  dump_data_dict[\"Lower Page\"][cell_idx-2]))\n",
    "            if cell_val == dump_data_dict[\"Lower Page\"][cell_idx-2]:\n",
    "                cell.style = same_sty\n",
    "            else:\n",
    "                cell.style = diff_sty\n",
    "                cell.value = cell_val + '/' + dump_data_dict[\"Lower Page\"][cell_idx-2]\n",
    "\n",
    "    print(\"Start compare Upper page data ...\")\n",
    "    for col in sheet.iter_cols(min_col=4, max_col=max_column, min_row=3, max_row=max_row-1):\n",
    "        dump_val = []\n",
    "        cell_idx = 0\n",
    "        PAGE_IN_DUMP = True\n",
    "        p = ''\n",
    "        #here only load dump upper data to list dump_val, \n",
    "        for cell in col:\n",
    "            if len(str(cell.value)) == 1:\n",
    "                p = '0' + str(cell.value)\n",
    "            else:\n",
    "                p = str(cell.value)\n",
    "\n",
    "            k = 'Upper Page '+ p + 'h'\n",
    "            # temp_summary_dict.update({k : v[1:]})\n",
    "            if k in dump_data_dict.keys():\n",
    "                dump_val = dump_data_dict[k]\n",
    "                PAGE_IN_DUMP = True\n",
    "            else:\n",
    "                PAGE_IN_DUMP = False\n",
    "            print('Load dump Upper data, ok')\n",
    "            break\n",
    "\n",
    "        #here start compare data\n",
    "        for cell in col:\n",
    "            cell_idx = cell_idx + 1\n",
    "            if cell_idx == 1:\n",
    "                continue\n",
    "\n",
    "            if PAGE_IN_DUMP:\n",
    "                if len(str(cell.value)) == 1:\n",
    "                    cell_val = '0' + str(cell.value)\n",
    "                else:\n",
    "                    cell_val = str(cell.value)\n",
    "\n",
    "                print(\"Upper Page {}h/{} Template Data:{} <---> Dump Data:{}\".format(p, 128 + (cell_idx-2), cell_val,  dump_val[cell_idx-2]))\n",
    "                if cell_val == dump_val[cell_idx-2]:\n",
    "                    cell.style = same_sty\n",
    "                else:\n",
    "                    cell.style = diff_sty\n",
    "                    cell.value = cell_val + '/' + dump_val[cell_idx-2]\n",
    "            else:\n",
    "                print(\"Not Found in Dump file, Keep color...\")\n",
    "\n",
    "    wb.move_sheet('Summary Copy', -1000)\n",
    "\n",
    "    outfile = template_file.split('.')[0] + '__&__' + dump_file.split('.')[0] + '.xlsm'\n",
    "    wb.save(filename=outfile)\n",
    "    return outfile\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    showVersion()\n",
    "    template_file, dump_file = selectCompareFiles()\n",
    "\n",
    "    print(\"You select Template file: {}, Dump file: {}\".format(template_file, dump_file))\n",
    "    outfile = startCompareFiles(template_file, dump_file)\n",
    "\n",
    "    print(\"All done. A new sheet 'Summary Copy' has been generated in \" + outfile + \"! Please Check.\")\n",
    "    print('')\n",
    "    print(\"Cell Color Meaning\")\n",
    "    print(\"Red:    Diff\")\n",
    "    print(\"Green:  Same\")\n",
    "    print(\"Yellow: Uncheck\")\n",
    "\n",
    "    close_win = input(\"Press any key to quit ...\")\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
